CREATE USER MSPETSHOP IDENTIFIED BY password DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS; 

GRANT CREATE SESSION TO MSPETSHOP; 

CREATE TABLE MSPETSHOP.ACCOUNT (
	USERID VARCHAR2(80) NOT NULL, 
	EMAIL VARCHAR2(80) NOT NULL, 
	FIRSTNAME VARCHAR2(80) NOT NULL, 
	LASTNAME VARCHAR2(80) NOT NULL, 
	STATUS VARCHAR2(2), 
	ADDR1 VARCHAR2(80) NOT NULL, 
	ADDR2 VARCHAR2(40), 
	CITY VARCHAR2(80) NOT NULL, 
	STATE VARCHAR2(80) NOT NULL, 
	ZIP VARCHAR2(20) NOT NULL, 
	COUNTRY VARCHAR2(20) NOT NULL, 
	PHONE VARCHAR2(80) NOT NULL, 
    CONSTRAINT PK_ACCOUNT PRIMARY KEY(USERID) );
    
CREATE TABLE MSPETSHOP.BANNERDATA (
	FAVCATEGORY VARCHAR2(80) NOT NULL, 
	BANNERDATA VARCHAR2(255), 
    CONSTRAINT PK_BANNERDATA PRIMARY KEY(FAVCATEGORY) );
    
CREATE TABLE MSPETSHOP.SUPPLIER (
    SUPPID int not null,
    NAME varchar(80) null,
    STATUS char(2) not null,
    ADDR1 varchar(80) null,
    ADDR2 varchar(80) null,
    CITY varchar(80) null,
    STATE varchar(80) null,
    ZIP char(5) null,
    PHONE varchar(80) null,
    constraint PK_SUPPLIER primary key (SUPPID) );

CREATE TABLE MSPETSHOP.SIGNON (
    USERNAME varchar(25) not null,
    PASSWORD varchar(25)  not null,
    constraint PK_SIGNON primary key (USERNAME) );

CREATE TABLE MSPETSHOP.PROFILE (
    USERID varchar(80) not null,
    LANGPREF varchar(80) not null,
    FAVCATEGORY varchar(30),
    MYLISTOPT int,
    BANNEROPT int,
    constraint PK_PROFILE primary key (USERID));

CREATE TABLE MSPETSHOP.CATEGORY (
    catid char(10) not null,
    name varchar(80) null,
    descn varchar(255) null,
    constraint PK_CATEGORY primary key (catid) ) ;

CREATE TABLE MSPETSHOP.product (
    productid char(10) not null,
    category char(10) not null,
    name varchar(80) null,
    descn varchar(255) null,
    constraint PK_PRODUCT primary key (productid),
        constraint FK_PRODUCT foreign key (category)
        references MSPETSHOP.CATEGORY (catid) );
        
CREATE TABLE MSPETSHOP.item (
    itemid char(10) not null,
    productid char(10) not null,
    listprice decimal(10,2) null,
    unitcost decimal(10,2) null,
    supplier int null,
    status char(2) null,
    attr1 varchar(80) null,
    attr2 varchar(80) null,
    attr3 varchar(80) null,
    attr4 varchar(80) null,
    attr5 varchar(80) null,
    constraint PK_ITEM primary key (itemid),
        constraint FK_ITEM_1 foreign key (productid)
        references MSPETSHOP.PRODUCT (productid),
        constraint FK_ITEM_2 foreign key (supplier)
        references MSPETSHOP.SUPPLIER (suppid) ) ;
        
CREATE TABLE MSPETSHOP.inventory (
    itemid char(10) not null,
    qty int not null,
    CONSTRAINT PK_INVENTORY PRIMARY KEY(itemid) ) ;